<?php
/* adminexport.php 2006-07-02 12:48:44
   Conference Registration - Registration for conferences - A XOOPS CMS Module
   Copyright (c) 2006 Kevin van Haaren, US
   URL:     http://www.superfantasmoworld.com/
   Contact: kevin@vanhaaren.net
 	
   XOOPS
   Copyright (c) 2000 XOOPS.org
   URL:     http://www.xoops.org/
    
   This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
    
 */
 
//The next comment block is for PHPDocumentor
/**
 * Conference Registration - Registration for conferences
 * 
 * adminexport.php
 * Export registration data to csv
 * 
 * @author Kevin van Haaren (kevin@vanhaaren.net)
 * @copyright 2006 Kevin van Haaren, US
 * @package CONFREG
 * @subpackage Admin_Function
 * 
 */

/**
* Do all the declarations etc needed by an admin page
*/
include_once "exportheader.inc";

$submit = strtolower($_POST['submit']);
if ($submit == 'export label data') {
	export_labels();
} elseif ($submit == 'export all data') {
	export_all();
} else {
	echo '<html><head><title>Error</title></head><body>';
	echo '<h3>Error in submit button</h3>';
	echo '<p>There was an error in the submit data, please contact the webmaster.</p>';
	echo '</body></html>';
}

function export_labels() {
	$quote = '"';
	$tab = "\t";
	$qt = $quote . $tab;
	$qtq = $quote . $tab . $quote;

	global $xoopsDB;

	$filename = "conference_nametags.xls";
	$dataHdl = $xoopsDB->query(	'SELECT p.userid, p.first_name, p.last_name, p.tmc_num,' . 
			' g.company, p.days_attending, p.cost, g.total_cost, g.AmountPaid, g.groupid'.
			'  FROM ' . $xoopsDB->prefix('confreg_people') . ' p' .
			'  JOIN ' . $xoopsDB->prefix('confreg_groups') . ' g' .
			'  ON p.groupid = g.groupid' .
			'  WHERE p.confid = 1 ' .
			'  ORDER BY g.company ASC, p.last_name ASC');

	$header = $quote . 'First Name'. $qtq . 'Last Name' . $qtq . 'Company' . $qtq . 'TMC Number' . $qtq. 'Days' . $qtq . 'Workshop 1' . $qtq . 'Workshop 2' . $qtq . 'Paid' . $qtq . 'Group Cost' . $qtq . 'Group ID' . $quote;
	$content = '';

	while ($data = $xoopsDB->fetchArray($dataHdl)) {
		$content .= $quote . $data['first_name'] . $qtq . $data['last_name'] . $qtq . $data['company'] . $qtq . $data['tmc_num'] .
			 $qtq. $data['days'] . $qt;

		$wshpH1 = $xoopsDB->query(	'SELECT confid, workshopid, title' .
				'  FROM ' . $xoopsDB->prefix('confreg_workshops') .
				'  WHERE ((confid = 1) AND (workshopid = ' . $data['w1'] . ')) ' );
		$w1=$xoopsDB->fetchArray($wshpH1);

		$wshpH2 = $xoopsDB->query(	'SELECT confid, workshopid, title' .
				'  FROM ' . $xoopsDB->prefix('confreg_workshops') .
				'  WHERE ((confid = 1) AND (workshopid = ' . $data['w2'] . ')) ' );
		$w2=$xoopsDB->fetchArray($wshpH2);

		if  (isset($w1['title']))  {
			$content .= $quote . $w1['title'] . $qt;
		} else {
			$content .= $quote . "" . $qt;
		}

		if  (isset($w2['title']))  {
			$content .= $quote . $w2['title'] . $qt;
		} else {
			$content .= $quote . "" . $qt;
		}

		$content .= $quote . (($data['TC'] == $data['AP']) ? 'Y' : 'N' ) . $qtq . '$' . $data['TC'] . '.00' . $qtq . $data['GID'] . $quote . "\r\n";
	}

	send_file($filename,$header . "\r\n" . $content . "\r\n");
}

function export_all() {
	$quote = '"';
	$tab = "\t";
	$qt = $quote . $tab . $quote;

	global $xoopsDB;
	
	$filename = "conference_data.xls";

	$dataHdl = $xoopsDB->query(	'SELECT g.groupid, g.company, g.paytype, g.bill_fname, g.bill_lname, g.billaddress1,' .
							' g.billcity, g.billstate, g.billzip, g.total_cost, g.paid, p.userid,' .
							' p.first_name, p.last_name, p.tmc_num, p.days_attending' .
							' FROM ' . $xoopsDB->prefix('confreg_people') . ' p' .
							' JOIN ' . $xoopsDB->prefix('confreg_groups') . ' g' .
							' ON p.groupid = g.groupid' .
							' WHERE p.confid = 1' .
							' ORDER BY g.company ASC, p.last_name ASC');


	$header = $quote . 'Confirmation' .  $qt . 'Company' . $qt . 'Pay Method' . $qt . 'Billing First Name' . $qt . 'Billing Last Name' .
	          $qt . 'Billing Address 1' . $qt . 'Billing City' . $qt . 'Billing State' . $qt . 'Billing Zip' .
	          $qt . 'First Name' . $qt . 'Last Name' . $qt . 'TMC Number' . $qt . 'Days Attending' .
	          $qt . 'Workshop 1' . $qt . 'Workshop 2' . $qt . 'Workshop 3' . $qt . 'Paid' . $quote;

	$content = '';

	while ($data = $xoopsDB->fetchArray($dataHdl)) {
		$content .= $quote . '200808' . $data['groupid'] . $qt . $data['company'] . $qt . $data['paytype'] . $qt . $data['bill_fname'] .
								$qt . $data['bill_lname'] . $qt . $data['billaddress1'] . $qt . $data['billcity'] .
								$qt . $data['billstate'] . $qt . $data['billzip'] .	$qt . $data['first_name'] .
								$qt . $data['last_name'] . $qt . $data['tmc_num'] .	$qt . $data['days'] . $quote . $tab;

		$wshpHdl = $xoopsDB->query(	'SELECT w.title, w.start_time' .
								' FROM ' . $xoopsDB->prefix('confreg_workshop_res') . ' r' .
								' JOIN ' . $xoopsDB->prefix('confreg_workshops') . ' w' .
								' ON r.workshopid = w.workshopid' .
								' WHERE ((r.confid = 1) AND (r.userid = ' . $data['userid'] . ')) ' .
								' ORDER BY w.start_time' );
		$count = 0;
		while ($shop = $xoopsDB->fetchArray($wshpHdl)) {
				$content .= $quote . $shop['title'] . $quote . $tab;
				$count++;
		}

		for ($i = (3 - $count ); $i > 0; $i--) {
			$content .= $tab;
		}
		
		if (($data['total_cost'] == $data['paid']) and (! empty($data['paid']))) {
			$content .= $quote . "Yes" . $quote . "\r\n";
		} else {
			$content .= $quote . "No" . $quote . "\r\n";
		}
	}
	send_file($filename,$header . "\r\n" . $content . "\r\n");
}

?>
